package com.es.dao.impl;

import com.es.JDBCUtils;
import com.es.dao.BankAccountDaoI;
import com.es.entity.BankAccount;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BankAccountDaoImpl implements BankAccountDaoI {
    @Override
    public int save(BankAccount bankAccount) {

        Connection connection = null;
        PreparedStatement ps = null;

        String sql = "insert into bank_account(name,id_card,card_no) values(?,?,?)";
        try {
            connection = JDBCUtils.getConnection("mysql");
            ps = connection.prepareStatement(sql);

            ps.setString(1, bankAccount.getName());
            ps.setString(2, bankAccount.getIdCard());
            ps.setString(3, bankAccount.getCardNo());
            return  ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(connection, ps, null);
        }

        return 0;
    }
    //脏读
    @Override
    public int withdraw(String cardNo, BigDecimal amount) {

        Connection connection = null;
        PreparedStatement ps = null;

        String sql = "update bank_account set balance = balance-? where card_no =?";

        try {
            connection = JDBCUtils.getConnection("mysql");
            connection.setAutoCommit(false);//开启事务
            ps = connection.prepareStatement(sql);
            ps.setBigDecimal(1, amount);
            ps.setString(2, cardNo);

            ps.executeUpdate();


            Thread.sleep(10000);

            int i = 10/0;
            connection.commit();//提交事务
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();

        } catch (InterruptedException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(connection,ps,null);
        }

        return 0;
    }

    @Override
    public int deposit(String cardNo, BigDecimal amount) {
        return 0;
    }

    @Override
    public int trans(String from, String to, BigDecimal amount) {
        //看事务的一致性
        Connection connection = null;
        PreparedStatement ps = null;


       // connection.setAutoCommit(false); //开启事务
        String sql = "update bank_account set balance=balance-? where card_no=?";
        String sql1 = "update bank_account set balance = balance+? where card_no = ?";
        try {
            connection = JDBCUtils.getConnection("mysql");
            connection.setAutoCommit(false); //开启事务
            ps = connection.prepareStatement(sql);
            ps.setBigDecimal(1,amount);
            ps.setString(2, from);
            //
            ps.executeUpdate();

            int i = 0;
            int j = 10/i;//

            ps = connection.prepareStatement(sql1);
            ps.setBigDecimal(1, amount);
            ps.setString(2, to);
            ps.executeUpdate();

            connection.commit();

        } catch (SQLException e) {
            try {
                connection.rollback();//事务回滚
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JDBCUtils.release(connection, ps, null);
        }

        return 0;
    }

    @Override
    public BankAccount get(String cardNo) {

        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;

        String sql = "select id,name,id_card,card_no,balance from bank_account where card_no = ?";

        try {
            connection = JDBCUtils.getConnection("mysql");
            //开启事务
            connection.setAutoCommit(false);
            //System.out.println(connection.getTransactionIsolation());
            //设置隔离级别
            connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
            //connection.setTransactionIsolation();
            ps = connection.prepareStatement(sql);
            ps.setString(1, cardNo);
            resultSet = ps.executeQuery();
            if(resultSet.next()){
                BankAccount bankAccount = new BankAccount();
                bankAccount.setId(resultSet.getInt(1));
                bankAccount.setName(resultSet.getString(2));
                bankAccount.setBalance(resultSet.getBigDecimal(5));
                bankAccount.setIdCard(resultSet.getString(3));
                bankAccount.setCardNo(resultSet.getString(4));
                System.out.println(bankAccount);
                return bankAccount;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(connection, ps, resultSet);
        }


        return null;
    }
}
